ALTER TABLE sms_template
ADD COLUMN `compare` varchar(50) NULL COMMENT '触发时间，单位：时，24<={currentTime-first_apply_time} 写为：24<=N，多条件之间，隔开' AFTER `template_content`;

ALTER TABLE `sms_task`
ADD COLUMN `content` varchar(500) NULL COMMENT '内容' AFTER `template_id`;

ALTER TABLE `sms_template`
ADD COLUMN `send_clock` varchar(50) NULL COMMENT '当天发送时刻' AFTER `template_content`;

ALTER TABLE `sms_send_log`
ADD COLUMN `status_code` varchar(20) NULL COMMENT '响应状态码' AFTER `msg_id`;

ALTER TABLE `sms_task`
MODIFY COLUMN `is_send` int(1) NOT NULL DEFAULT 0 COMMENT '是否发送 0未发送 1发送成功 2发送失败 3跳过不发' AFTER `schedule_time`;


SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sms_recall
-- ----------------------------
DROP TABLE IF EXISTS `sms_recall`;
CREATE TABLE `sms_recall` (
  `id` bigint(32) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(50) NOT NULL COMMENT 'UserId',
  `first_apply_type` varchar(50) NOT NULL DEFAULT '0' COMMENT '首次申请类型，0注册 1实名 2授信 ',
  `message_apply_step` int(2) NOT NULL DEFAULT '0' COMMENT '申请次数',
  `first_apply_time` datetime DEFAULT NULL COMMENT '首次申请时间',
  `remark` varchar(255) DEFAULT NULL,
  `create_by` varchar(20) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_by` varchar(20) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='召回短信';

-- ----------------------------
-- Records of sms_recall
-- ----------------------------
BEGIN;
INSERT INTO `sms_recall` VALUES (1, 'cd51c0fdeca64ee7a2dc82108cc29c07', 'recall_register', 0, '2021-08-23 15:28:49', NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sms_recall` VALUES (2, 'cd51c0fdeca64ee7a2dc82108cc29c07', 'recall_loan', 0, '2021-09-11 17:25:43', NULL, NULL, NULL, NULL, NULL);
INSERT INTO `sms_recall` VALUES (3, 'cd51c0fdeca64ee7a2dc82108cc29c07', 'recall_kyc', 0, '2021-09-16 11:42:33', NULL, NULL, '2021-09-14 11:48:20', NULL, '2021-09-16 11:42:33');
INSERT INTO `sms_recall` VALUES (4, '162587e527ad457a87a985ed7fd0695f', 'recall_kyc', 0, '2021-09-14 11:42:33', NULL, NULL, '2021-09-15 11:39:21', NULL, '2021-09-16 11:42:33');
INSERT INTO `sms_recall` VALUES (5, 'acb70003b7474a82bf13a9ff17e2bbb4', 'recall_loan', 0, '2021-09-16 11:42:33', NULL, NULL, '2021-09-16 11:42:33', NULL, NULL);
INSERT INTO `sms_recall` VALUES (6, '71205af872f94ac0ab48661b22612056', 'recall_credit', 0, '2021-09-15 11:42:33', NULL, NULL, '2021-09-15 11:42:33', NULL, NULL);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;




BEGIN;

DELETE FROM sms_template WHERE sms_type IN ( 'credit_pass','loan_success');

INSERT INTO `sms_template` VALUES (4, 'InfoBipx', 'notice', 'loan_success', '100004', '放款成功通知', 'Dear #lastName# , the #loanAmt#  loan you applied for at LinkCredit has been successfully disbursed, #repaymentDate# is your due date, please make your repayment on time and maintain good credit, it will help to increase your limit.', NULL, NULL, '1', NULL, NULL, '2021-03-23 10:51:09', NULL, '2021-09-17 16:00:04');
INSERT INTO `sms_template` VALUES (7, 'InfoBipx', 'notice', 'credit_pass', '100007', '授信通过通知', 'Dear #lastName#, you have successfully got a LinkCredit limit, the current available credit limit is #creditLine#, enter LinkCredit to complete the withdrawal.', NULL, NULL, '1', NULL, NULL, '2021-04-09 11:26:18', NULL, '2021-09-17 16:38:14');

INSERT INTO `sms_template` VALUES (26, 'infoBipx', 'recall', 'recall_register', '200001', '召回短信-注册未实名1', 'Dear customers, login to LinkCredit from now and complete your identity verification for a chance to receive up to ₦50,000 credit with no collateral.	', '', '24<=N,N<=48', '1', NULL, NULL, '2021-09-06 14:48:18', NULL, '2021-09-17 10:13:35');
INSERT INTO `sms_template` VALUES (27, 'infoBipx', 'recall', 'recall_register', '200002', '召回短信-注册未实名2', 'Dear customer, you are already registered with LinkCredit and only need the last 2 steps to get up to ₦50,000 Naira credit. Login to the app and apply now!	', '', '48<N', '1', NULL, NULL, '2021-09-06 14:48:18', NULL, '2021-09-17 10:13:52');
INSERT INTO `sms_template` VALUES (28, 'infoBipx', 'recall', 'recall_kyc', '200003', '召回短信-实名未授信1', 'Dear #lastName#, it only takes 5 minutes to complete the line of credit application and get a loan from LinkCredit immediately, just login to the app and finish.	', '', '24<=N,N<=48', '1', NULL, NULL, '2021-09-06 14:48:18', NULL, '2021-09-17 10:14:11');
INSERT INTO `sms_template` VALUES (29, 'infoBipx', 'recall', 'recall_kyc', '200004', '召回短信-实名未授信2', 'Dear #lastName#, congratulations, only 1 step to complete your limit application and get your loan right away, login to LinkCredit and apply now!	', '', '48<N', '1', NULL, NULL, '2021-09-06 14:48:18', NULL, '2021-09-17 10:14:30');
INSERT INTO `sms_template` VALUES (30, 'infoBipx', 'recall', 'recall_credit', '200005', '召回短信-授信未提现1', 'Dear #lastName#, you have your LinkCredit credit limit, now login to the app and apply for a  withdrawal, the money will be transferred in seconds.	', '', '24<=N,N<=48', '1', NULL, NULL, '2021-09-06 14:48:18', NULL, '2021-09-17 10:14:46');
INSERT INTO `sms_template` VALUES (31, 'infoBipx', 'recall', 'recall_credit', '200006', '召回短信-授信未提现2', 'Dear #lastName#, you have credit limit from LinkCredit, Login to the app and request a withdrawal now!	', '', '48<N', '1', NULL, NULL, '2021-09-06 14:48:18', NULL, '2021-09-17 10:14:57');
INSERT INTO `sms_template` VALUES (32, 'infoBipx', 'repayment', 'repayment_T-N', '200009', '14天产品T-7还款提醒；14天产品T-5还款提醒；7天/14天产品T-3还款提醒', 'Dear #lastName#, #repaymentDate# is the repayment date in your loan. Please repay via LinkCredit APP on time.', '', NULL, '1', '14天产品T-7还款提醒；\n\n14天产品T-5还款提醒；\n\n7天/14天产品T-3还款提醒', NULL, '2021-09-15 17:55:43', NULL, '2021-09-17 11:56:37');
INSERT INTO `sms_template` VALUES (33, 'infoBipx', 'repayment', 'repayment_T-1', '200010', '7天/14天产品T-1还款提醒', 'Dear #lastName#, you loan with #RemainingAmt# will due tomorrow . Please repay via LinkCredit APP on time to avoid bad influence on your credit.', '', NULL, '1', '{lastName}:客户姓氏\n\n{RemainingAmt} = {needRepaidAmt}-{RepaidPrincipal}-{RepaidServicefee}-{RepaidInterest}-{RepaidLatefee} : 剩余应还金额', NULL, '2021-09-15 17:57:15', NULL, '2021-09-17 11:56:38');
INSERT INTO `sms_template` VALUES (34, 'infoBipx', 'repayment', 'repayment_T+0', '200011', '7天/14天产品T+0还款提醒', 'Dear #lastName#, you loan with #RemainingAmt# will due today . Please repay via LinkCredit APP or transfer to your unique account #VAaccountnumber#(#BankName#) today. A poor repayment record will decrease your credit limit.', '', NULL, '1', '{lastName}:客户姓氏\n\n{RemainingAmt} = {needRepaidAmt}-{RepaidPrincipal}-{RepaidServicefee}-{RepaidInterest}-{RepaidLatefee} : 剩余应还金额\n\n{VAaccountnumber}: Monnify虚拟还款账号\n\n{BankName}: 银行名称', NULL, '2021-09-15 17:59:05', NULL, '2021-09-17 11:56:38');
INSERT INTO `sms_template` VALUES (35, 'infoBipx', 'repayment', 'repayment_T+1', '200012', '7天/14天产品T+1还款提醒', 'Dear #lastName#, your loan with #RemainingAmt# has been overdued 1 day. Please repay via LinkCredit App or transfer to your unique account #VAaccountnumber#(#BankName#) today. Defaulting loans will be reported to National Credit Bureaus and this can severely affect your ability to access loans in future.', '', NULL, '1', '{lastName}:客户姓氏\n\n{RemainingAmt} = {needRepaidAmt}-{RepaidPrincipal}-{RepaidServicefee}-{RepaidInterest}-{RepaidLatefee} : 剩余应还金额\n\n{VAaccountnumber}: Monnify虚拟还款账号\n\n{BankName}: 银行名称', NULL, '2021-09-15 18:01:57', NULL, '2021-09-17 11:56:39');
INSERT INTO `sms_template` VALUES (36, 'infoBipx', 'repayment', 'repayment_T+3', '200013', '7天/14天产品T+3还款提醒', 'Dear #lastName#, your loan with #RemainingAmt# has been overdued 3 days. Please repay via LinkCredit App or transfer to your unique account #VAaccountnumber#(#BankName#) today. Default interest will increase daily and defaulting loans will be reported to National Credit Bureaus and this can severely affect your ability to access loans in future.', '', NULL, '1', '{lastName}:客户姓氏\n\n{RemainingAmt} = {needRepaidAmt}-{RepaidPrincipal}-{RepaidServicefee}-{RepaidInterest}-{RepaidLatefee} : 剩余应还金额\n\n{VAaccountnumber}: Monnify虚拟还款账号\n\n{BankName}: 银行名称', NULL, '2021-09-15 18:03:25', NULL, '2021-09-17 11:56:40');
INSERT INTO `sms_template` VALUES (37, 'infoBipix', 'repayment', 'repayment_T+5', '200014', '7天/14天产品T+5还款提醒', 'Dear #lastName#, your loan with #RemainingAmt# has been overdued 5 days. Please repay via LinkCredit App or transfer to your unique account #VAaccountnumber#(#BankName#) today. Defaulting loans will be reported to National Credit Bureaus and this can severely affect your ability to access loans in future and your family or your friends will be called if you do not make the repayment today.', '', NULL, '1', '{lastName}:客户姓氏\n\n{RemainingAmt} = {needRepaidAmt}-{RepaidPrincipal}-{RepaidServicefee}-{RepaidInterest}-{RepaidLatefee} : 剩余应还金额\n\n{VAaccountnumber}: Monnify虚拟还款账号\n\n{BankName}: 银行名称', NULL, '2021-09-15 18:04:28', NULL, '2021-09-17 15:29:28');
INSERT INTO `sms_template` VALUES (38, 'infoBipix', 'notice', 'repayment_outstanding', '200015', '还款-借据正常', 'Dear #lastName#, your payment of #rapaymentAmt# is #repaystatus# . Your loan still have remaining with #remainingAmt#, please repay before #repaymentDate#. ', NULL, NULL, '0', NULL, NULL, '2021-09-17 17:59:01', NULL, '2021-09-17 18:02:50');
INSERT INTO `sms_template` VALUES (39, 'infoBipix', 'notice', 'repayment_overdue', '200016', '还款-借据逾期', 'Dear #lastName#, your payment of #rapaymentAmt# is #repaystatus#. Please repay the remaining amount #remainingAmt# today via LinkCredit App or transfer to your unique account {VAaccountnumber}(#BankName#). Defaulting loans will be reported to National Credit Bureaus and this can severely affect your ability to access loans in future.', NULL, NULL, '0', NULL, NULL, '2021-09-17 18:00:46', NULL, '2021-09-17 18:00:46');
INSERT INTO `sms_template` VALUES (40, 'infoBipix', 'notice', 'repayment_finish', '200017', '还款-借据结清', 'Dear #lastName#, your payment of #rapaymentAmt# is success and loan #contractNo# is settled. Welcome to apply another loan via LinkCredit APP.', NULL, NULL, '0', NULL, NULL, '2021-09-17 18:01:48', NULL, '2021-09-17 18:01:48');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

